﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Mail;
using Fiveesoft.Utility;

namespace NLUNZ.Business
{

    public class Repository
    {
        public static int SLIDESHOW_WIDTH = 960;
        public static int SLIDESHOW_HEIGHT = 278;
        public static int PARTNERIMG_WIDTH = 100;
        public static int PARTNERIMG_HEIGHT = 60;
        public static int ADS_WIDTH = 320;
        public static int ADS_HEIGHT = 330;

        private static string webServiceUrl =
            ConfigurationManager.AppSettings["trackingApiUrl"];
        //"http://www.51kdd.com/api/xml/result/?com={0}&kw={1}&validate=&key=FREE-KEY-FGJ794GN";

        public static string DBConnection = //"Data Source=localhost;Initial Catalog=NLUWebDB;Integrated Security=True";
            ConfigurationManager.ConnectionStrings["NLUWebDBConnectionString"].ToString();

        #region slide images
        public static List<SlideImage> GetActiveSlideImages()
        {
            using (NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {

                var slideImages = from s in context.SlideImages
                                  where s.IsActive == true
                                  orderby s.OrderNumber
                                  select s;
                return slideImages.ToList();
            }

        }

        #endregion

        #region Custom

        public static List<Custom> GetAllCustom()
        {
            using (NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {
                var customs = from c in context.Customs
                              select c;
                return customs.ToList();
            }
        } 

        #endregion

        #region partners

        public static List<Partner> GetAllPartners()
        {
            using (NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {

                var partners = from p in context.Partners
                               where (p.showInHomePage == true)
                               select p;
                return partners.ToList();
            }
        }


        public static void AddPartner(Partner partner)
        {
            using (NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {

                context.Partners.InsertOnSubmit(partner);
                context.SubmitChanges();
            }

        }

        #endregion

        #region News

        public static List<CompanyInfo> GetLatestNews(int count)
        {
            using (NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {
                var news = (from n in context.CompanyInfos
                            orderby n.CreateDate descending
                            select n).Take(count);
                return news.ToList();

            }

        }

        #endregion

        #region Load Ads

        public static string LoadRandomImageUrl()
        {
            using(NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {
                int numberOfBanners = context.Banners.Select(p=>p).Count();
                if(numberOfBanners == 0)
                    return string.Empty;
                Random random = new Random();
                int num = random.Next(1, numberOfBanners+1);
                IQueryable<Banner> banner = context.Banners.Skip(num - 1).Take(1);
                var firstOrDefault = banner.FirstOrDefault();
                if (firstOrDefault != null) return firstOrDefault.Content;
                return string.Empty;
            }
        }

        #endregion

        public static void AddBooking(Booking booking)
        {
            using (NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {

                context.Bookings.InsertOnSubmit(booking);
                context.SubmitChanges();
            }

        }

        public static void SendEmail(string subject, string body)
        {
            var smtpServer = ConfigurationManager.AppSettings["smtpServer"];
            var smtpServerPort = Convert.ToInt32(ConfigurationManager.AppSettings["smptServerPort"]);
            var emailAccount = ConfigurationManager.AppSettings["emailAccount"];
            var emailPassword = ConfigurationManager.AppSettings["emailPassword"];
            var adminEmail = ConfigurationManager.AppSettings["adminEmail"];


            var client = new SmtpClient(smtpServer, smtpServerPort)
            {
                Credentials = new NetworkCredential(emailAccount, emailPassword),
                EnableSsl = true
            };
            MailMessage message = new MailMessage(emailAccount, adminEmail, subject, body);
            message.IsBodyHtml = true;
            message.Priority = MailPriority.High;
            client.Send(message);
        }

        public static TrackingItem DeliveryTrackingItemMapping(Delivery delivery)
        {

            TrackingItem item = new TrackingItem();

            //pickup
            item.PickupDateTime = delivery.ReceivedDateTime == null ? DateTime.MinValue : (DateTime)delivery.ReceivedDateTime;
            item.TIPickupBy = delivery.Recipient;

            //inventory in warehouse
            item.InventoryDateTime = delivery.InventoryDateTime == null
                                         ? DateTime.MinValue
                                         : (DateTime)delivery.InventoryDateTime;

            //arrival departure airport
            item.ArrivalDepartureAirportDateTime = delivery.AirportDateTime == null
                                       ? DateTime.MinValue
                                       : (DateTime)delivery.AirportDateTime;
            //delivery
            item.DeliveryToDestinationCountryCustomDateTime = delivery.DeliveryDateTime == null
                                        ? DateTime.MinValue
                                        : (DateTime)delivery.DeliveryDateTime;
            //custom
            item.ClearCustomDateTime = delivery.CustomDate == null ? DateTime.MinValue : (DateTime)delivery.CustomDate;


            item.TIDeliveryNumber = delivery.DeliveryNum;
            item.TIDeliveryCity = delivery.DeliveryCity;
            item.TICustom = delivery.Custom;

            #region courier delivery

            List<CourierTracking> couriers = new List<CourierTracking>();
            //get tracking details by deliverynumber

            using(NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {
                var trackings = from t in context.Trackings
                                where t.DeliveryNumber == delivery.DeliveryNum
                                select t;
                if (trackings.Count() != 0)
                {
                    foreach (Tracking tracking in trackings)
                    {
                        CourierTracking ct = new CourierTracking();
                        ct.CourierCode = tracking.CompanyCode;
                        ct.CourierCompanyName = tracking.Company.Name;
                        ct.CourierTrackingNumber = tracking.TrackingNumber;
                        ct.CourierDateTime = tracking.TrackingDateTime == null
                                                 ? string.Empty
                                                 : ((DateTime)tracking.TrackingDateTime).ToString("yyyy-MM-dd HH:ss:mm");
                        ct.CourierTrackingCode = tracking.Company.CompanyTrackingCode;
                        //ct.TrackingXML = GetTrackingXML(ct);
                        couriers.Add(ct);
                    }
                }                
            }

            item.CourierTrackings = couriers;

            #endregion

            return item;
        }

        private static string GetTrackingXML(CourierTracking ct)
        {
            string apiKey = ConfigurationManager.AppSettings["TrackingApiKey"];
            string url = string.Format(webServiceUrl, apiKey, ct.CourierTrackingCode, ct.CourierTrackingNumber);

            WebResponse response;
            try
            {
                WebRequest request = WebRequest.Create(url);
                response = request.GetResponse();
            }
            catch (Exception)
            {
                return "ERROR: 数据传输中断， 请稍后重试。";
            }
            Stream stream = response.GetResponseStream();

            StreamReader reader = new StreamReader(stream);
            return reader.ReadToEnd();
        }

        public static string GetValueFromDictionary(string key)
        {
            using (NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {

                var value = context.Dictionaries.SingleOrDefault(d => d.Key.ToUpper() == key.ToUpper());

                return value == null ? string.Empty : value.Value;
            }
        }

        public static bool KeyExistInDictionary(string key)
        {
            bool isExist = false;
            using (NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {

                var value = context.Dictionaries.Count(d => d.Key.ToUpper() == key.ToUpper());
                isExist = value > 0;
            }
            return isExist;
        }

        public static void InsertKeyToDictionary(string key)
        {
            using(NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {
                Dictionary dict = new Dictionary() {Key = key, Value = "Title: <br/>Body:<br/>"};
                context.Dictionaries.InsertOnSubmit(dict);
                context.SubmitChanges();
            }
        }

        public static CompanyInfo GetNewsById(int id)
        {
            using(NLUDBDataContext context = new NLUDBDataContext(DBConnection))
            {
                CompanyInfo news = context.CompanyInfos.Single(n => n.ID == id);
                return news;                
            }


        }

        public static int GetCustomID(string custom)
        {
            using(var context = new NLUDBDataContext(DBConnection))
            {
                Custom ctx = context.Customs.SingleOrDefault(c => c.Name.Equals(custom));
                if(ctx != null)
                    return ctx.ID;
            }
            return 0;
        }

        public static string GetCustomByID(int id)
        {
            using(var ctx = new NLUDBDataContext(DBConnection))
            {
                Custom custom = ctx.Customs.SingleOrDefault(c => c.ID == id);
                if (custom != null)
                    return custom.Name;
            }
            return string.Empty;
        }

        public static void InsertSideshow(string title, string name, string url, string desc, string imgUrl)
        {
            using(var context = new NLUDBDataContext(DBConnection))
            {
                                
                SlideImage slideImage = new SlideImage();
                slideImage.Title = title;
                slideImage.Name = name;
                slideImage.LinkUrl = url;
                slideImage.ImageUrl = imgUrl;
                slideImage.Description = desc;
                slideImage.CreateDate = DateTime.Now;
                slideImage.OrderNumber = 0;
                slideImage.IsActive = true;

                context.SlideImages.InsertOnSubmit(slideImage);
                context.SubmitChanges();
            }

        }


        public static void InsertBanner(string title, string imgUrl)
        {
            using (var context = new NLUDBDataContext(DBConnection))
            {
                Banner banner = new Banner();
                banner.Title = string.IsNullOrEmpty(title.Trim()) ? imgUrl : title;
                banner.Content = imgUrl;
                banner.CreateDate = DateTime.UtcNow.AddHours(12);

                context.Banners.InsertOnSubmit(banner);
                context.SubmitChanges();
            }

        }
        public static void InsertPartner(string partnerTitle, string partnerUrl, string imgUrl)
        {
            using(var context = new NLUDBDataContext(DBConnection))
            {
                Partner partner = new Partner();
                partner.Title = partnerTitle;
                partner.LinkUrl = partnerUrl;
                partner.ImageUrl = imgUrl;
                partner.showInHomePage = true;
                partner.CreateDate = DateTime.Now;
                context.Partners.InsertOnSubmit(partner);
                context.SubmitChanges();                
            }


        }

        public static List<Delivery> GetDeliveryById(List<string> deliveryId)
        {
            using(var context = new NLUDBDataContext(DBConnection))
            {
                var deliveries = new List<Delivery>();
                foreach (string id in deliveryId)
                {
                    Delivery delivery = context.Deliveries.SingleOrDefault(d => d.DeliveryNum.ToUpper() == id.Trim().ToUpper());
                    if (delivery != null)
                        deliveries.Add(delivery);
                }
                return deliveries;
            }

        }

        public static bool DeliveryNumberExist(string deliveryNum)
        {
            if (String.IsNullOrEmpty(deliveryNum.Trim()))
                return false;
            using (var context = new NLUDBDataContext(DBConnection))
            {
                var delivery = from d in context.Deliveries
                               where d.DeliveryNum.ToUpper() == deliveryNum.ToUpper()
                               select d;
                return delivery.Any();
            }
        }

        public static Delivery GetDeliveryByDeliveryNumber(string deliveryNum)
        {
            if (String.IsNullOrEmpty(deliveryNum.Trim()))
                return null;
            using (var context = new NLUDBDataContext(DBConnection))
            {
                var delivery = from d in context.Deliveries
                               where d.DeliveryNum.ToUpper() == deliveryNum.ToUpper()
                               select d;
                if (delivery.Any())
                    return delivery.ToList()[0];
            }
            return null;
        }

        public static bool InsertDeliveryRecordSuccess(Delivery delivery)
        {
            try
            {
                using (var context = new NLUDBDataContext(DBConnection))
                {
                    context.Deliveries.InsertOnSubmit(delivery);
                    context.SubmitChanges();
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }


        public static bool InsertDeliveryRecordBatch(Delivery delivery,int batchLogId)
        {
            try
            {
                using (var context = new NLUDBDataContext(DBConnection))
                {
                    context.Deliveries.InsertOnSubmit(delivery);

                    BatchUpdateLog log = new BatchUpdateLog();
                    log.DeliveryNumber = delivery.DeliveryNum;
                    log.LogBatchID = batchLogId;
                    log.LogType = "Delivery";

                    context.BatchUpdateLogs.InsertOnSubmit(log);
                    context.SubmitChanges();
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool DeleteDeliveryBatchRecord(int batchLogId)
        {
            using (var context = new NLUDBDataContext(DBConnection))
            {
                var batches = context.BatchUpdateLogs.Where(b => b.LogBatchID == batchLogId);
                List<Delivery> deliveries = new List<Delivery>();
                foreach (BatchUpdateLog batchUpdateLog in batches)
                {
                    string deliveryNum = batchUpdateLog.DeliveryNumber;
                    var delivery =
                        context.Deliveries.FirstOrDefault(d => d.DeliveryNum.ToLower().Equals(deliveryNum.ToLower()));
                    if(delivery != null)
                        deliveries.Add(delivery);
                }
                if(deliveries.Count > 0)
                    context.Deliveries.DeleteAllOnSubmit(deliveries);
                context.BatchUpdateLogs.DeleteAllOnSubmit(batches);
                context.SubmitChanges();
            }
            return true;
        }

        public static bool DeleteTrackingBatchRecord(int batchLogId)
        {
            using (var context = new NLUDBDataContext(DBConnection))
            {
                var batches = context.BatchUpdateLogs.Where(b => b.LogBatchID == batchLogId);
                List<Tracking> trackings = new List<Tracking>();
                foreach (BatchUpdateLog batchUpdateLog in batches)
                {
                    string deliveryNum = batchUpdateLog.DeliveryNumber;
                    string trackingNum = batchUpdateLog.TrackingNumber;
                    var tracking =
                        from t in context.Trackings
                        where
                            t.DeliveryNumber.ToLower().Equals(deliveryNum.ToLower()) &&
                            t.TrackingNumber.ToLower().Equals(trackingNum.ToLower())
                        select t;
                    Tracking tr = tracking.FirstOrDefault();
                    if (tr != null)
                        trackings.Add(tr);
                }
                if (trackings.Count > 0)
                    context.Trackings.DeleteAllOnSubmit(trackings);
                context.BatchUpdateLogs.DeleteAllOnSubmit(batches);
                context.SubmitChanges();
            }
            return true;
        }

        public static bool UpdateDeliveryInfo(Delivery delivery)
        {
            try
            {
                using (var context = new NLUDBDataContext(DBConnection))
                {
                    if(Repository.DeliveryNumberExist(delivery.DeliveryNum))
                    {
                        Delivery existingRecord = context.Deliveries.Single(d => d.DeliveryNum == delivery.DeliveryNum);

                        existingRecord.Custom = delivery.Custom;
                        existingRecord.AirportDateTime = delivery.AirportDateTime;
                        existingRecord.DeliveryDateTime = delivery.DeliveryDateTime;

                        context.SubmitChanges();
                    }else
                    {
                        return false;
                    }
                }
            }
            catch (Exception exception)
            {
                return false;
            }
            return true;
        }

        public static bool UpdateDeliveryRecordSuccess(Delivery delivery)
        {
            try
            {
                using (var context = new NLUDBDataContext(DBConnection))
                {
                    Delivery existingRecord = context.Deliveries.Single(d => d.DeliveryNum == delivery.DeliveryNum);

                    existingRecord.ReceivedDateTime = delivery.ReceivedDateTime;
                    existingRecord.Recipient = delivery.Recipient;
                    existingRecord.DeliveryCity = delivery.DeliveryCity;
                    existingRecord.Custom = delivery.Custom;
                    existingRecord.InventoryDateTime = delivery.InventoryDateTime;
                    existingRecord.AirportDateTime = delivery.AirportDateTime;
                    existingRecord.DeliveryDateTime = delivery.DeliveryDateTime;
                    //existingRecord.CustomDate = delivery.CustomDate;

                    context.SubmitChanges();
                }
            }
            catch (Exception exception)
            {
                return false;
            }
            return true;
        }
        public static bool UpdateDeliveryRecord(Delivery delivery)
        {
            try
            {
                using (var context = new NLUDBDataContext(DBConnection))
                {
                    Delivery existingRecord = context.Deliveries.Single(d => d.DeliveryNum.ToUpper() == delivery.DeliveryNum.ToUpper());

                    existingRecord.ReceivedDateTime = delivery.ReceivedDateTime;
                    existingRecord.Recipient = delivery.Recipient;
                    existingRecord.DeliveryCity = delivery.DeliveryCity;
                    existingRecord.Custom = delivery.Custom;
                    existingRecord.InventoryDateTime = delivery.InventoryDateTime;
                    existingRecord.AirportDateTime = delivery.AirportDateTime;
                    existingRecord.DeliveryDateTime = delivery.DeliveryDateTime;
                    existingRecord.CustomDate = delivery.CustomDate;
                    existingRecord.IsCheckedOut = delivery.IsCheckedOut;

                    context.SubmitChanges();
                }
            }
            catch (Exception exception)
            {
                return false;
            } 
            return true;
        }
        public static bool UpdateDeliveryRecordCustomInformation(Delivery delivery)
        {
            //only customDate and IsCheckedOut is updated
            try
            {
                using (var context = new NLUDBDataContext(DBConnection))
                {
                    Delivery existingDelivery = context.Deliveries.Single(d => d.DeliveryNum.ToUpper() == delivery.DeliveryNum.ToUpper());

                    existingDelivery.IsCheckedOut = delivery.IsCheckedOut;
                    existingDelivery.CustomDate = delivery.CustomDate;

                    context.SubmitChanges();
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool IsTrackingExist(string deliveryNumber, string trackingNumber)
        {
            using(var context = new NLUDBDataContext(DBConnection))
            {
                
                var tracking = from t in context.Trackings
                               where t.DeliveryNumber.ToUpper() == deliveryNumber.ToUpper() && t.TrackingNumber.ToUpper() == trackingNumber.ToUpper()
                               select t;
                return tracking.Any();
            }
        }

        public static bool InsertTrackingRecord(Tracking tracking, out string errorMessage)
        {
            try
            {
                using (var ctx = new NLUDBDataContext(DBConnection))
                {
                    ctx.Trackings.InsertOnSubmit(tracking);
                    ctx.SubmitChanges();
                }
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message + ex.StackTrace;
                return false;
            }
            errorMessage = string.Empty;
            return true;
        }
        public static bool InsertTrackingRecordBatch(Tracking tracking, int batchLogId, out string errorMessage)
        {
            try
            {
                using(var ctx = new NLUDBDataContext(DBConnection))
                {
                    ctx.Trackings.InsertOnSubmit(tracking);

                    BatchUpdateLog log = new BatchUpdateLog();
                    log.DeliveryNumber = tracking.DeliveryNumber;
                    log.TrackingNumber = tracking.TrackingNumber;
                    log.LogBatchID = batchLogId;
                    log.LogType = "Tracking";

                    ctx.BatchUpdateLogs.InsertOnSubmit(log);
                    ctx.SubmitChanges();
                }
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message + ex.StackTrace;
                return false;
            }
            errorMessage = string.Empty;
            return true;
        }

        public static bool UpdateTrackingRecord(Tracking tracking)
        {
            try
            {
                using (var ctx = new NLUDBDataContext(DBConnection))
                {
                    Tracking trk = ctx.Trackings.Single(
                        t => t.DeliveryNumber.ToUpper() == tracking.DeliveryNumber.ToUpper() && t.TrackingNumber.ToUpper() == tracking.TrackingNumber.ToUpper());
                    trk.CompanyCode = tracking.CompanyCode;
                    trk.TrackingDateTime = tracking.TrackingDateTime;

                    ctx.SubmitChanges();
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        #region NoticeBoard 

        public static List<String> GetLatestNoticeBoard()
        {
            return GetLatestNoticeBoard(20);
        }

        public static List<String> GetLatestNoticeBoard(int maxItems)
        {
            List<string> notices = new List<string>();
            try
            {
                using(var ctx = new NLUDBDataContext(DBConnection))
                {
                    var latestNb = ctx.NoticeBoards.OrderByDescending(n => n.CreateDate).Select(o => o).Take(maxItems);
                    foreach (NoticeBoard noticeBoard in latestNb)
                    {
                        notices.Add(noticeBoard.Title);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return notices;
        }

        public static MessageBag InsertNoticeBoardItem(NoticeBoard notice)
        {
            MessageBag message = new MessageBag() {Verified = true};
            try
            {
                using (var context = new NLUDBDataContext(DBConnection))
                {
                    context.NoticeBoards.InsertOnSubmit(notice);
                    context.SubmitChanges();
                }
            }
            catch (Exception ex)
            {
                message.Verified = false;
                message.ErrorMessage = ex.Message;
            }
            return message;
        }

        #endregion


        public static MessageBag  MembershipAuthentication(string username,string password)
        {
            var messageBag = new MessageBag {Verified = false};
            try
            {
                using(var ctx = new NLUDBDataContext(DBConnection))
                {
                    //get userid from Users table
                    var user =
                        ctx.Users.SingleOrDefault(
                            u => u.UserName.Equals(username.ToLower()));
                    if(user == null)
                        throw new Exception("User doesn't exist.");

                    int id = user.UserId;
                    Repository.UpdateUserActivity(id);
                    var membership = ctx.Memberships.SingleOrDefault(m => m.UserId == id);
                    if(membership == null)
                        throw new Exception("User is not a member, please contact administrator.");
                    if (!membership.Password.Equals(password))
                        throw new Exception("Password is not correct.");
                    messageBag.Verified = true;
                }
            }
            catch (Exception ex)
            {
                messageBag.ErrorMessage = ex.Message;
            }
            return messageBag;
        }

        private static void UpdateUserActivity(int id)
        {
             using(var ctx = new NLUDBDataContext(DBConnection))
             {
                 User user = ctx.Users.FirstOrDefault(u => u.UserId == id);
                 if(user != null)
                 {
                     user.LastActivityDate = DateTime.UtcNow.AddHours(12);
         
                     ctx.SubmitChanges();
                 }
             }
        }

        public static MessageBag MembershipAuthorization(string username, string roleName)
        {
            var bag = new MessageBag {Verified = false};
            try
            {
                using(var ctx = new NLUDBDataContext(DBConnection))
                {
                    var user =
                        ctx.Users.SingleOrDefault(
                            u => u.UserName.ToLower().Equals(username.ToLower()));
                    if(user != null)
                    {
                        var role =
                            ctx.Roles.SingleOrDefault(
                                r => r.RoleName.ToLower().Equals(roleName.ToLower()));
                        if(role != null)
                        {
                            int userId = user.UserId;
                            int roleId = role.RoleId;

                            var userIsInRole =
                                ctx.UsersInRoles.SingleOrDefault(uir => uir.RoleId == roleId && uir.UserId == userId);
                            if (userIsInRole != null)
                                bag.Verified = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                bag.ErrorMessage = ex.Message;
            }
            return bag;
        }
    
        public static MessageBag Register(string username, string password, string email)
        {
            MessageBag message = new MessageBag() {Verified = false};
            try
            {
                //check Username exist in the system and do insert
                if(IsUserNameAvailable(username))
                {
                    int userId = InsertNewUser(username.ToLower());
                    InsertNewMembership(userId, password, email);
                    message.Verified = true;
                }
                else
                {
                    message.ErrorMessage = "Username is not available.";
                }
            }
            catch (Exception ex)
            {
                message.ErrorMessage = ex.Message;
            }
            return message;
        }

        public static MessageBag DeleteUser(string username)
        {
            MessageBag bag = new MessageBag() {Verified = false};
            try
            {
                
                using(var ctx = new NLUDBDataContext(DBConnection))
                {
                    var user = ctx.Users.SingleOrDefault(u => u.UserName == username);
                    if(user != null)
                    {
                        int userId = user.UserId;
                        //delete user from membership
                        var membership = ctx.Memberships.SingleOrDefault(m => m.UserId == userId);
                        if(membership != null)
                        {
                            ctx.Memberships.DeleteOnSubmit(membership);
                            ctx.SubmitChanges();
                        }
                        //delete user record from usersinroles
                        IQueryable<UsersInRole> uir = from u in ctx.UsersInRoles
                                                     where u.UserId == userId
                                                     select u;
                        if(!uir.Any())
                        {
                            ctx.UsersInRoles.DeleteAllOnSubmit(uir);
                            ctx.SubmitChanges();
                        }
                        //delete user record from users 
                        ctx.Users.DeleteOnSubmit(user);
                        ctx.SubmitChanges();
                        bag.Verified = true;
                    }
                }
            }
            catch (Exception ex)
            {
                bag.ErrorMessage = ex.Message;
            }
            return bag;
        }

        public static MessageBag AssociateMembershipRoles(string username, List<int> rolesId)
        {
            MessageBag message = new MessageBag() {Verified = false};
            try
            {
                using(var ctx = new NLUDBDataContext(DBConnection))
                {
                    var user =
                        ctx.Users.SingleOrDefault(
                            u => u.UserName.Equals(username.ToLower()));
                    if(user != null)
                    {
                        if(rolesId.Count == 0)
                            throw new Exception("No Role is assigned to this user.");
                        foreach (int roleId in rolesId)
                        {
                            UsersInRole uir = new UsersInRole() {RoleId = roleId, UserId = user.UserId};
                            ctx.UsersInRoles.InsertOnSubmit(uir);
                            ctx.SubmitChanges();
                        }
                    }
                    else
                    {
                        throw new Exception("User doesn't exist in the system.");
                    }
                }
            }
            catch (Exception ex)
            {
                message.ErrorMessage = ex.Message;
            }
            return message;
        }

        public static List<Role> GetUserAssociatedRoles(string username)
        {
            List<Role> roles = new List<Role>();
            try
            {
                using(var ctx = new NLUDBDataContext(DBConnection))
                {
                    var user =
                        ctx.Users.SingleOrDefault(
                            u => u.UserName.Equals(username.ToLower()));
                    if(user != null)
                    {
                        var uirs = from u in ctx.UsersInRoles
                                   where u.UserId == user.UserId
                                   select u;
                        foreach (UsersInRole uir in uirs)
                        {
                            Role role = ctx.Roles.SingleOrDefault(r=>r.RoleId == uir.RoleId);
                            if(role != null)
                                roles.Add(role);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return roles;
        }

        public static MessageBag ChangePassword(string username, string oldPassword, string newPassword)
        {
            MessageBag message = new MessageBag() {Verified = false};
            try
            {
                //check old password is correct.

                using(var ctx = new NLUDBDataContext(DBConnection))
                {
                    var user =
                        ctx.Users.SingleOrDefault(
                            u => u.UserName.Equals(username.ToLower()));
                    if(user == null)
                        throw new Exception("User doesn't exist.");
                    var membership = ctx.Memberships.SingleOrDefault(m => m.UserId == user.UserId);
                    if(membership == null)
                        throw new Exception("This user is not a member of the site.");
                    if (!membership.Password.Equals(oldPassword, StringComparison.CurrentCulture))
                        message.ErrorMessage = "Old Password is incorrect. Please check your password and try again.";

                    //update password
                    membership.Password = newPassword;
                    membership.LastPasswordChangedDate = DateTime.UtcNow.AddHours(12);
                    ctx.SubmitChanges();
                    message.Verified = true;
                }
            }
            catch (Exception ex)
            {
                message.ErrorMessage = ex.Message;
            }
            return message;
        }

        private static void InsertNewMembership(int userId, string password, string email)
        {
            try
            {
                using(var ctx = new NLUDBDataContext(DBConnection))
                {
                    Membership membership = new Membership()
                                                {
                                                    UserId = userId,
                                                    Email = email,
                                                    Password = password,
                                                    CreateDate = DateTime.UtcNow.AddHours(12)
                                                };
                    ctx.Memberships.InsertOnSubmit(membership);
                    ctx.SubmitChanges();
                }
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
        }

        private static int InsertNewUser(string username)
        {
            int userId;
            try
            {
                using(var ctx = new NLUDBDataContext(DBConnection))
                {
                    User user = new User() {UserName = username,};
                    ctx.Users.InsertOnSubmit(user);
                    ctx.SubmitChanges();

                    userId = user.UserId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return userId;
        }

        public static bool IsUserNameAvailable(string username)
        {
            using (var ctx = new NLUDBDataContext(DBConnection))
            {
                var user =
                    ctx.Users.SingleOrDefault(
                        u => u.UserName.Equals(username.ToLower()));
                if (user == null)
                    return true;
                return false;
            }
        }

        public static bool IsUserActive(string username)
        {
            //check username is active and is not deleted
            using (var ctx = new NLUDBDataContext(DBConnection))
            {
                var user =
                    ctx.Users.Where(u => u.IsActive == true).SingleOrDefault(
                        u => u.UserName.ToLower().Equals(username.ToLower()));
                if (user == null)
                    return false;
                return true;

            }
        }
        public static string GetUserEmailByUsername(string username)
        {
            using (var ctx = new NLUDBDataContext(DBConnection))
            {
                var user =
                    ctx.Users.Where(u => u.IsActive == true).SingleOrDefault(
                        u => u.UserName.ToLower().Equals(username.ToLower()));
                if(user != null)
                {
                    var membership = ctx.Memberships.FirstOrDefault(m => m.UserId == user.UserId);
                    if (membership != null)
                        return membership.Email;
                }

            }
            return string.Empty;
        }

        public static string GetUserPasswordByUsername(string username)
        {
            using (var ctx = new NLUDBDataContext(DBConnection))
            {
                var user =
                    ctx.Users.Where(u => u.IsActive == true).SingleOrDefault(
                        u => u.UserName.ToLower().Equals(username.ToLower()));
                if (user != null)
                {
                    var membership = ctx.Memberships.FirstOrDefault(m => m.UserId == user.UserId);
                    if (membership != null)
                        return membership.Password;
                }

            }
            return string.Empty;
        }
        public static List<City> GetCities()
        {
            using(var ctx = new NLUDBDataContext(DBConnection))
            {
                var cities = ctx.Cities.Select(o=>o);
                return cities.ToList();
            }
        } 

        public static IEnumerable<BatchUpdateTrack> GetAllBatchUpdateTrack()
        {
            IEnumerable<BatchUpdateTrack> result = null;
            using (var ctx = new NLUDBDataContext())
            {
                var query = from batch in ctx.BatchUpdateLogs
                            orderby batch.LogBatchID descending
                            group batch by batch.LogBatchID
                                into grouping
                                select new { LogID = grouping.Key };
                List<BatchUpdateTrack> logs = new List<BatchUpdateTrack>();
                foreach (var q in query)
                {
                    int id = q.LogID;
                    var firstOrDefault = ctx.BatchUpdateLogs.FirstOrDefault(b => b.LogBatchID == id);

                    if (firstOrDefault != null)
                    {
                        string type = firstOrDefault.LogType;
                        logs.Add(new BatchUpdateTrack() { UpdateTrackID = id, UpdateType = type });
                    }
                }
                result = logs.OrderByDescending(l => l.UpdateTrackID);
            }
            return result;
        }


    }
}